using DBUtil.SqlSegment;
using DotNetCommon.Extensions;
using System.Collections.Generic;

namespace DBUtil.Provider.MySql.SqlSegment;

/// <summary>
/// 数据库字符串运算符
/// </summary>
public class MySqlStringSqlSegment(DBAccess db) : StringSqlSegment(db)
{
    /// <inheritdoc/>
    public override string GetLength(string sqlSeg, bool null2Zero = false)
    {
        if (null2Zero) return $"length(ifnull({sqlSeg},''))";
        else return $"length({sqlSeg})";
    }
    /// <inheritdoc/>
    public override string Trim(string sqlSeg) => $"trim({sqlSeg})";
    /// <summary>
    /// 左右两侧trim指定字符串, 这个功能sqlserver支持比较复杂<br/>
    /// 参考: <seealso href="https://blog.csdn.net/u010476739/article/details/151458692"/><br/>
    /// 如果考虑是否支持大小写就...(事实上, 一般我们在翻译 "".Contains("") 也没有在乎过 db 和 c# 在大小写之间的差异)
    /// </summary>
    public string Trim(string sqlSeg, string patSeg) => $"trim(both {patSeg} from {sqlSeg})";
    /// <inheritdoc/>
    public override string LeftTrim(string sqlSeg) => $"ltrim({sqlSeg})";
    /// <summary>
    /// 左侧trim指定字符串, 这个功能sqlserver支持比较复杂<br/>
    /// 参考: <seealso href="https://blog.csdn.net/u010476739/article/details/151458692"/><br/>
    /// 如果考虑是否支持大小写就...(事实上, 一般我们在翻译 "".Contains("") 也没有在乎过 db 和 c# 在大小写之间的差异)
    /// </summary>
    public string LeftTrim(string sqlSeg, string patSeg) => $"trim(leading {patSeg} from {sqlSeg})";
    /// <inheritdoc/>
    public override string RightTrim(string sqlSeg) => $"rtrim({sqlSeg})";
    /// <summary>
    /// 右侧trim指定字符串, 这个功能sqlserver支持比较复杂<br/>
    /// 参考: <seealso href="https://blog.csdn.net/u010476739/article/details/151458692"/><br/>
    /// 如果考虑是否支持大小写就...(事实上, 一般我们在翻译 "".Contains("") 也没有在乎过 db 和 c# 在大小写之间的差异)
    /// </summary>
    public string RightTrim(string sqlSeg, string patSeg) => $"trim(trailing {patSeg} from {sqlSeg})";
    /// <inheritdoc/>
    public override string Upper(string sqlSeg) => $"upper({sqlSeg})";
    /// <inheritdoc/>
    public override string Lower(string sqlSeg) => $"lower({sqlSeg})";
    /// <inheritdoc/>
    public override string ReplaceAll(string inputSqlSeg, string oldStrSqlSeg, string newStrSqlSeg) => $"replace({inputSqlSeg},{oldStrSqlSeg},{newStrSqlSeg})";
    /// <inheritdoc/>
    public override string SplitFirst(string inputSqlSeg, string patternSqlSeg) => $"substring_index({inputSqlSeg}, {patternSqlSeg}, 1)";
    /// <inheritdoc/>
    public override string SplitLast(string inputSqlSeg, string patternSqlSeg) => $"substring_index({inputSqlSeg}, {patternSqlSeg}, -1)";
    /// <summary>
    /// 按照 patternSqlSeg 分割后, 取结果数组的第 posIndex(从0开始) 个
    /// </summary>
    public string SplitSpecified(string inputSqlSeg, string patternSqlSeg, int posIndex) => $"substring_index({inputSqlSeg}, {patternSqlSeg}, {posIndex + 1})";
    /// <inheritdoc/>
    /// <remarks>
    /// 注意: 为什么用 concat_ws 而不是 concat? 为了和 c# 的行为保持一致
    /// <list type="bullet">
    /// <item>c#中两个null(string)相加得到的是 ""</item>
    /// <item>concat(null,a) => null, 而 concat_ws('',null,'a') => 'a'</item>
    /// </list>
    /// </remarks>
    public override string Add(string inputSqlSeg, string input2SqlSeg) => $"concat_ws('',{inputSqlSeg},{input2SqlSeg})";
    //为什么有 canReturnNull 参数, 当相加的结果只在db中运算时,就可能没必要保证不返回null
    /// <inheritdoc/>
    public override string AddMulti(IEnumerable<string> inputSqlSegs, bool canReturnNull = false) => canReturnNull ? $"concat_ws({inputSqlSegs.ToStringSeparated(",")})" : $"concat_ws('',{inputSqlSegs.ToStringSeparated(",")})";
    /// <inheritdoc/>
    public override string SubString(string sqlSeg, int start, int len) => $"substr({sqlSeg},{start + 1},{len})";
    /// <summary>
    /// 仿切片, 类似 "abcdfe"[1..4] => "bcd"
    /// </summary>
    public string Slice(string sqlSeg, int start, int end)
    {
        //"abcdef"[1..4] => "bcd" => substr(seg, 2, 3)
        //"abcdef"[-5..4] => "bcd" => substr(seg, length(seg)-5+1, 3) => substr('abcdef',2,3)
        //"abcdef"[-5..-2] => "bcd" => substr(seg, length(seg)-5+1, 3) => substr('abcdef',2,3)
        //"abcdef"[1..-2] => "bcd" => substr(seg, length(seg)-5+1, 3) => substr('abcdef',2,3)

        var startStr = "";
        if (start >= 0)
        {
            //db从1开始
            //"abcdef"[1..
            startStr = $"{start + 1}";
        }
        else
        {
            //"abcdfe"[-5..
            startStr = $"length({sqlSeg}){start}+1";
        }
        var lenStr = "";
        if (end > 0)
        {
            if (start >= 0) lenStr = $"{end - start}";//"abcdef"[1..4]
            else lenStr = $"{end}-(length({sqlSeg}){start})";//"abcdef"[-5..4]
        }
        else
        {
            if (start >= 0) lenStr = $"length({sqlSeg}){end}-{start}";//"abcdef"[1..-2]
            else lenStr = $"{end - start}";//"abcdef"[-5..-2]
        }
        return $"substr({sqlSeg},{startStr},{lenStr})";
    }

    /// <summary>
    /// 按照 pattern 分割后,如果有多个项,则移除第一个<br />
    /// 应用场景: 参照: <seealso cref="SplitSkipLastIfMultiple"/><br/>
    /// 示例:<br/>
    /// <list type="bullet">
    /// <item>"xiaoming@hong@localhost" => "hong@localhost"</item>
    /// <item>"xiaoming@hong" => "hong"</item>
    /// <item>"xiaoming" => "xiaoming"</item>
    /// </list>
    /// </summary>
    public string SplitSkipFirstIfMultiple(string inputSqlSeg, string patternSqlSeg) => $"substring({inputSqlSeg},instr({inputSqlSeg},{patternSqlSeg})+1)";

    /// <summary>
    /// 按照 pattern 分割后,如果有多个项,则移除最后一个<br />
    /// 应用场景1: 去除文件后缀名<br />
    /// 应用场景2: 去除user()函数后的ip<br />
    /// <list type="bullet">
    /// <item>"xiaoming@hong@localhost" => "xiaoming@hong"</item>
    /// <item>"xiaoming@hong" => "xiaoming"</item>
    /// <item>"xiaoming" => "xiaoming"</item>
    /// </list>
    /// </summary>
    public string SplitSkipLastIfMultiple(string inputSqlSeg, string patternSqlSeg) => $"reverse(substring(reverse({inputSqlSeg}),instr(reverse({inputSqlSeg}),{patternSqlSeg})+1))";
    /// <inheritdoc/>
    public override string IndexOf(string inputSqlSeg, string patternSqlSeg, bool ignoreCase = true)
    {
        return $"(locate({patternSqlSeg},{(ignoreCase == false ? "binary " : "")}{inputSqlSeg})-1)";
    }
    /// <inheritdoc/>
    public override string IsNullOrEmpty(string inputSqlSeg)
    {
        return $"({inputSqlSeg} is null or {inputSqlSeg} = '')";
    }
    /// <inheritdoc/>
    public override string IsNullOrWhiteSpace(string inputSqlSeg)
    {
        return $"({inputSqlSeg} is null or ltrim({inputSqlSeg}) = '')";
    }
}
